package com.library.web.book;

import com.library.dao.BookDAO;
import com.library.model.Book;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.MultipartConfig;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import jakarta.servlet.http.Part;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@MultipartConfig(
        fileSizeThreshold = 1024 * 1024, // 1MB
        maxFileSize = 1024 * 1024 * 10,   // 10MB
        maxRequestSize = 1024 * 1024 * 20 // 20MB
)
@WebServlet("/book")
public class BookServlet extends HttpServlet {
    private BookDAO bookDAO = new BookDAO();
    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    private SimpleDateFormat excelDateSdf = new SimpleDateFormat("yyyy/MM/dd");

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        String action = request.getParameter("action");

        if (action == null || "list".equals(action)) {
            String title = request.getParameter("title");
            String author = request.getParameter("author");
            String isbn = request.getParameter("isbn");
            String publisher = request.getParameter("publisher");

            List<Book> books = bookDAO.findByCondition(title, author, isbn, publisher);
            request.setAttribute("books", books);
            request.getRequestDispatcher("book/book.jsp").forward(request, response);
        } else if ("delete".equals(action)) {
            try {
                int id = Integer.parseInt(request.getParameter("id"));
                bookDAO.delete(id);
            } catch (NumberFormatException e) {
                e.printStackTrace();
            }
            response.sendRedirect("book?action=list");
        } else if ("exportExcel".equals(action)) {
            exportBooksToExcel(request, response);
        } else if ("downloadTemplate".equals(action)) {
            downloadUploadTemplate(response);
        }
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        String action = request.getParameter("action");

        if ("add".equals(action)) {
            try {
                Book book = new Book();
                book.setIsbn(request.getParameter("isbn"));
                book.setTitle(request.getParameter("title"));
                book.setAuthor(request.getParameter("author"));
                book.setPublisher(request.getParameter("publisher"));
                book.setPublishDate(sdf.parse(request.getParameter("publishDate")));
                book.setCreatedAt(new Date()); // 设置创建时间为当前时间
                bookDAO.add(book);
            } catch (ParseException e) {
                e.printStackTrace();
                request.setAttribute("errorMsg", "日期格式错误，请使用yyyy-MM-dd");
                request.getRequestDispatcher("book/book.jsp").forward(request, response);
                return;
            }
            response.sendRedirect("book?action=list");
        } else if ("update".equals(action)) {
            try {
                Book book = new Book();
                book.setId(Integer.parseInt(request.getParameter("id")));
                book.setIsbn(request.getParameter("isbn"));
                book.setTitle(request.getParameter("title"));
                book.setAuthor(request.getParameter("author"));
                book.setPublisher(request.getParameter("publisher"));
                book.setPublishDate(sdf.parse(request.getParameter("publishDate")));
                bookDAO.update(book);
            } catch (ParseException e) {
                e.printStackTrace();
                request.setAttribute("errorMsg", "日期格式错误，请使用yyyy-MM-dd");
                request.getRequestDispatcher("book/book.jsp").forward(request, response);
                return;
            }
            response.sendRedirect("book?action=list");
        } else if ("importExcel".equals(action)) {
            importBooksFromExcel(request, response);
        }
    }

    // 导出图书列表到Excel
    private void exportBooksToExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String title = request.getParameter("title");
        String author = request.getParameter("author");
        String isbn = request.getParameter("isbn");
        String publisher = request.getParameter("publisher");
        List<Book> books = bookDAO.findByCondition(title, author, isbn, publisher);

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("图书列表");

        // 表头
        String[] headers = {"序号", "ISBN", "书名", "作者", "出版社", "出版日期", "创建时间"};
        Row headerRow = sheet.createRow(0);
        CellStyle headerStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        headerStyle.setFont(font);

        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
            sheet.autoSizeColumn(i);
        }

        // 填充数据
        SimpleDateFormat datetimeSdf = new SimpleDateFormat("yyyy/MM/dd HH:mm");
        for (int i = 0; i < books.size(); i++) {
            Book book = books.get(i);
            Row row = sheet.createRow(i + 1);

            row.createCell(0).setCellValue(i + 1);
            row.createCell(1).setCellValue(book.getIsbn());
            row.createCell(2).setCellValue(book.getTitle());
            row.createCell(3).setCellValue(book.getAuthor());
            row.createCell(4).setCellValue(book.getPublisher());
            row.createCell(5).setCellValue(book.getPublishDate() != null ?
                    excelDateSdf.format(book.getPublishDate()) : "");
            row.createCell(6).setCellValue(book.getCreatedAt() != null ?
                    datetimeSdf.format(book.getCreatedAt()) : "");
        }

        // 响应设置
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        String fileName = "图书列表_" + System.currentTimeMillis() + ".xlsx";
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");

        try (OutputStream os = response.getOutputStream()) {
            workbook.write(os);
            os.flush();
        } finally {
            workbook.close();
        }
    }

    // 下载上传模板
    private void downloadUploadTemplate(HttpServletResponse response) throws IOException {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("图书上传模板");

            // 表头（带必填标识）
            String[] headers = {"ISBN*", "书名*", "作者*", "出版社*", "出版日期(格式：yyyy/MM/dd)"};
            Row headerRow = sheet.createRow(0);
            CellStyle headerStyle = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setBold(true);
            headerStyle.setFont(font);

            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(headerStyle);
                sheet.autoSizeColumn(i);
            }

            // 示例数据
            Row exampleRow = sheet.createRow(1);
            exampleRow.createCell(0).setCellValue("9787115546081");
            exampleRow.createCell(1).setCellValue("Java编程思想");
            exampleRow.createCell(2).setCellValue("Bruce Eckel");
            exampleRow.createCell(3).setCellValue("机械工业出版社");
            exampleRow.createCell(4).setCellValue("2020/01/15");

            // 响应设置
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            String fileName = "图书上传模板.xlsx";
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");

            try (OutputStream os = response.getOutputStream()) {
                workbook.write(os);
                os.flush();
            }
        }
    }

    // 从Excel导入图书
    private void importBooksFromExcel(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        Part filePart = request.getPart("excelFile");
        String fileName = filePart.getSubmittedFileName();

        // 验证文件格式
        if (!fileName.endsWith(".xlsx") && !fileName.endsWith(".xls")) {
            request.setAttribute("errorMsg", "请上传Excel格式文件（.xlsx或.xls）");
            request.getRequestDispatcher("book/book.jsp").forward(request, response);
            return;
        }

        List<Book> books = new ArrayList<>();
        List<String> errors = new ArrayList<>();

        // 解析Excel
        try (InputStream is = filePart.getInputStream();
             Workbook workbook = WorkbookFactory.create(is)) {

            Sheet sheet = workbook.getSheetAt(0);
            int rowNum = 0;

            for (Row row : sheet) {
                rowNum++;
                if (rowNum == 1) continue; // 跳过表头

                // 验证必填字段
                if (row.getCell(0) == null || getCellStringValue(row.getCell(0)).trim().isEmpty() ||
                        row.getCell(1) == null || getCellStringValue(row.getCell(1)).trim().isEmpty() ||
                        row.getCell(2) == null || getCellStringValue(row.getCell(2)).trim().isEmpty() ||
                        row.getCell(3) == null || getCellStringValue(row.getCell(3)).trim().isEmpty()) {
                    errors.add("第" + rowNum + "行：ISBN、书名、作者、出版社为必填项");
                    continue;
                }

                try {
                    Book book = new Book();
                    book.setIsbn(getCellStringValue(row.getCell(0)).trim());
                    book.setTitle(getCellStringValue(row.getCell(1)).trim());
                    book.setAuthor(getCellStringValue(row.getCell(2)).trim());
                    book.setPublisher(getCellStringValue(row.getCell(3)).trim());

                    // 处理出版日期（非必填）
                    if (row.getCell(4) != null) {
                        String dateStr = getCellStringValue(row.getCell(4)).trim();
                        if (!dateStr.isEmpty()) {
                            book.setPublishDate(excelDateSdf.parse(dateStr));
                        }
                    }

                    book.setCreatedAt(new Date()); // 设置当前时间为创建时间
                    books.add(book);
                } catch (ParseException e) {
                    errors.add("第" + rowNum + "行：日期格式错误，请使用yyyy/MM/dd");
                } catch (Exception e) {
                    errors.add("第" + rowNum + "行：解析错误 - " + e.getMessage());
                }
            }

        } catch (Exception e) {
            errors.add("文件解析失败：" + e.getMessage());
        }

        // 处理导入结果
        if (!errors.isEmpty()) {
            // 拼接错误信息（最多显示5条，避免过长）
            StringBuilder errorMsg = new StringBuilder();
            int showCount = Math.min(errors.size(), 5);
            for (int i = 0; i < showCount; i++) {
                errorMsg.append(errors.get(i)).append("；");
            }
            if (errors.size() > 5) {
                errorMsg.append("...共").append(errors.size()).append("条错误");
            }
            request.setAttribute("errorMsg", errorMsg.toString());
            request.getRequestDispatcher("book/book.jsp").forward(request, response);
        } else {
            // 保存数据到数据库
            int successCount = 0;
            for (Book book : books) {
                try {
                    bookDAO.add(book);
                    successCount++;
                } catch (Exception e) {
                    errors.add("ISBN为[" + book.getIsbn() + "]的图书添加失败：" + e.getMessage());
                }
            }

            if (!errors.isEmpty()) {
                request.setAttribute("errorMsg", "部分图书导入失败：" + String.join("；", errors));
            } else {
                request.setAttribute("successMsg", "成功导入" + successCount + "本图书");
            }
            request.getRequestDispatcher("book/book.jsp").forward(request, response);
        }
    }

    // 工具方法：获取单元格的字符串值（兼容各种单元格类型）
    private String getCellStringValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return excelDateSdf.format(cell.getDateCellValue());
                } else {
                    // 数字类型转为字符串（避免科学计数法）
                    return String.valueOf(cell.getNumericCellValue()).replaceAll("\\.0$", "");
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
}
